package zy.dao.sys.workbench.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sys.workbench.WorkBenchDAO;
import zy.entity.common.todo.Common_ToDo;
import zy.entity.sys.menu.T_Sys_Menu;
import zy.entity.sys.workbench.T_Sys_WorkBench;
import zy.entity.vip.set.T_Vip_ReturnSetUp;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class WorkBenchDAOImpl extends BaseDaoImpl implements WorkBenchDAO{
	
	@Override
	public List<T_Sys_Menu> listUpMenu(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT mn_id,mn_code,mn_name ");
		sql.append(" FROM t_sys_menu t");
		sql.append(" JOIN t_sys_rolemenu rm ON rm_mn_code = mn_code ");
		sql.append(" WHERE 1=1");
		sql.append(" AND mn_child = 'Y'");
		sql.append(" AND mn_state = 0");
		sql.append(" AND rm_state = 0");
		sql.append(" AND INSTR(mn_version,:mn_version)>0");
		sql.append(" AND INSTR(mn_shop_type,:shop_type)>0");
		sql.append(" AND rm_ro_code = :ro_code");
		sql.append(" AND rm.companyid = :companyid");
		sql.append(" GROUP BY mn_id");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sys_Menu.class));
	}
	
	@Override
	public List<T_Sys_Menu> listSubMenu(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT mn_id,mn_code,mn_name ");
		sql.append(" FROM t_sys_menu t");
		sql.append(" LEFT JOIN t_sys_workbench wb ON wb_mn_id = mn_id AND wb_type IN(0,1) AND wb_us_id = :us_id AND wb.companyid = :companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND wb_id IS NULL");
		sql.append(" AND mn_state = 0");
		sql.append(" AND mn_child = 'N'");
		if(StringUtil.isNotEmpty(params.get("mn_upcode"))){
			sql.append(" AND mn_upcode = :mn_upcode");
		}
		sql.append(" AND INSTR(mn_version,:mn_version)>0");
		sql.append(" AND INSTR(mn_shop_type,:shop_type)>0");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sys_Menu.class));
	}
	
	@Override
	public List<Common_ToDo> listToDo(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT td_id,td_name ");
		sql.append(" FROM common_todo t");
		sql.append(" LEFT JOIN t_sys_workbench wb ON wb_mn_id = td_id AND wb_type = 2 AND wb_us_id = :us_id AND wb.companyid = :companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND wb_id IS NULL");
		sql.append(" AND td_state = 0");
		sql.append(" AND INSTR(td_version,:td_version)>0");
		sql.append(" AND INSTR(td_shop_type,:shop_type)>0");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(Common_ToDo.class));
	}

	@Override
	public Integer count(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_sys_workbench t");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(params.get("wb_type"))) {
			sql.append(" AND wb_type = :wb_type ");
		}
		if (StringUtil.isNotEmpty(params.get("wb_us_id"))) {
			sql.append(" AND wb_us_id = :wb_us_id ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Sys_WorkBench> list(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT wb_id,wb_us_id,wb_mn_id,wb_oper_usid,wb_sysdate,wb_weight,wb_type,companyid,");
		sql.append(" IF(wb_type=2,(SELECT td_name FROM common_todo td WHERE td_id = wb_mn_id LIMIT 1),(SELECT mn_name FROM t_sys_menu mn WHERE mn_id = wb_mn_id LIMIT 1)) AS mn_name");
		sql.append(" FROM t_sys_workbench t");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(params.get("wb_type"))) {
			sql.append(" AND wb_type = :wb_type ");
		}
		if (StringUtil.isNotEmpty(params.get("wb_us_id"))) {
			sql.append(" AND wb_us_id = :wb_us_id ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(StringUtil.isNotEmpty(params.get("start"))){
			sql.append(" LIMIT :start,:end");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sys_WorkBench.class));
	}
	
	@Override
	public List<T_Sys_WorkBench> listByUser(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT wb_id,wb_us_id,wb_mn_id,wb_oper_usid,wb_sysdate,wb_weight,wb_type,companyid,");
		sql.append(" mn_name,mn_url,mn_style,mn_icon");
		sql.append(" FROM t_sys_workbench t");
		sql.append(" JOIN t_sys_menu mn ON wb_mn_id = mn_id");
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(params.get("wb_type"))) {
			sql.append(" AND wb_type = :wb_type ");
		}else {
			sql.append(" AND wb_type IN (0,1)");
		}
		sql.append(" AND wb_us_id = :wb_us_id ");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sys_WorkBench.class));
	}
	
	@Override
	public List<T_Sys_WorkBench> listToDoByUser(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT wb_id,wb_us_id,wb_mn_id,wb_oper_usid,wb_sysdate,wb_weight,wb_type,companyid,");
		sql.append(" td_name AS mn_name,td_url AS mn_url,td_style AS mn_style,td_icon AS mn_icon");
		sql.append(" FROM t_sys_workbench t");
		sql.append(" JOIN common_todo td ON wb_mn_id = td_id");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND wb_type = 2");
		sql.append(" AND wb_us_id = :wb_us_id ");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sys_WorkBench.class));
	}

	@Override
	public Integer check(List<Integer> mnIds,Integer wb_type, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT COUNT(1) ");
		sql.append(" FROM t_sys_workbench t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND wb_mn_id IN (:mnIds) ");
		sql.append(" AND wb_type = :wb_type ");
		sql.append(" AND wb_us_id = :wb_us_id ");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("mnIds", mnIds).addValue("wb_type", wb_type).addValue("wb_us_id", us_id).addValue("companyid", companyid), 
				Integer.class);
	}
	
	@Override
	public void save(List<T_Sys_WorkBench> workBenchs) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sys_workbench");
		sql.append(" (wb_us_id,wb_mn_id,wb_oper_usid,wb_sysdate,wb_weight,wb_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:wb_us_id,:wb_mn_id,:wb_oper_usid,:wb_sysdate,:wb_weight,:wb_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(workBenchs.toArray()));
	}
	
	@Override
	public void delete(Integer wb_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sys_workbench");
		sql.append(" WHERE wb_id=:wb_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("wb_id", wb_id));
	}
	
	@Override
	public void delete(List<Integer> ids) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sys_workbench");
		sql.append(" WHERE wb_id IN(:ids)");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ids", ids));
	}
	
	@Override
	public Map<String, Object> statToDoWarn(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Map<String, Object> resultMap = new HashMap<String, Object>();
		final Map<String, String> identityMap = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT td_id,td_identity");
		sql.append(" FROM common_todo");
		sql.append(" WHERE td_id IN(:ids)");
		namedParameterJdbcTemplate.query(sql.toString(), params, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(java.sql.ResultSet rs, int rowNum) throws java.sql.SQLException {
				identityMap.put(rs.getString("td_id"), rs.getString("td_identity"));
				return null;
			};
		});
		if(identityMap.isEmpty()){
			return resultMap;
		}
		for (String key : identityMap.keySet()) {
			String identity = identityMap.get(key);
			
			resultMap.put(key, 22);
			
			
			if(CommonUtil.TODO_IDENTITY_WANTWARN.equals(identity)){//店铺发货单
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_shop_want t");
				sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
				sql.append(getShopSQL(shop_type, 1));
				sql.append(" WHERE 1 = 1");
				sql.append(" AND (wt_ar_state IN (1,5) OR (wt_ar_state = 0 AND wt_applyamount = 0 AND (wt_ar_usid IS NULL OR wt_ar_usid = :us_id)))");
				sql.append(" AND wt_type = 0 ");
				sql.append(" AND t.companyid=:companyid");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}else if(CommonUtil.TODO_IDENTITY_WANTWARN_SELF.equals(identity)){//补货申请单
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_shop_want t");
				sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_code = :shop_code");
				sql.append(" AND (wt_ar_state IN (3) OR (wt_ar_state = 0 AND wt_applyamount != 0 AND (wt_ar_usid IS NULL OR wt_ar_usid = :us_id)))");
				sql.append(" AND wt_type = 0 ");
				sql.append(" AND t.companyid=:companyid");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}else if(CommonUtil.TODO_IDENTITY_WANTRETURNWARN.equals(identity)){//店铺退货单
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_shop_want t");
				sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
				if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
					sql.append(getShopSQL(shop_type, 1));
					sql.append(" WHERE 1 = 1");
					sql.append(" AND (wt_ar_state IN (3) OR (wt_ar_state = 0 AND (wt_ar_usid IS NULL OR wt_ar_usid = :us_id)))");
				}else{//自营
					sql.append(" WHERE 1 = 1");
					sql.append(" AND sp_code = :shop_code");
					sql.append(" AND (wt_ar_state IN (1,5) OR (wt_ar_state = 0 AND (wt_ar_usid IS NULL OR wt_ar_usid = :us_id)))");
				}
				sql.append(" AND wt_type = 1 ");
				sql.append(" AND t.companyid=:companyid");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}else if(CommonUtil.TODO_IDENTITY_BATCHSELLWARN.equals(identity)){//批发出库单审核
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_batch_sell t");
				sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
				sql.append(" WHERE 1=1");
				sql.append(" AND ci_sp_code = :shop_code ");//上级店铺编号
				sql.append(" AND se_ar_state = 0 ");
				sql.append(" AND se_type = 0 ");
				sql.append(" AND t.companyid=:companyid");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}else if(CommonUtil.TODO_IDENTITY_BATCHRETURNWARN.equals(identity)){//批发退货单审核
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_batch_sell t");
				sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
				sql.append(" WHERE 1=1");
				sql.append(" AND ci_sp_code = :shop_code ");//上级店铺编号
				sql.append(" AND se_ar_state = 0 ");
				sql.append(" AND se_type = 1 ");
				sql.append(" AND t.companyid=:companyid");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}else if(CommonUtil.TODO_IDENTITY_PRODUCTRETURNDATE.equals(identity)){//退换货提醒
				
			}else if(CommonUtil.TODO_IDENTITY_CONSUMEVISIT.equals(identity)){//消费回访
				sql.setLength(0);
				sql.append("SELECT rts_id,rts_day,rts_second,rts_name,rts_shop_code,t.companyid  ");
				sql.append(" FROM t_vip_returnsetup t");
				sql.append(" WHERE 1=1 ");
				sql.append(" AND rts_shop_code = :shop_code");
				sql.append(" AND t.companyid = :companyid ");
				sql.append(" ORDER BY rts_second");
				List<T_Vip_ReturnSetUp> setUps = namedParameterJdbcTemplate.query(sql.toString(), params, 
						new BeanPropertyRowMapper<>(T_Vip_ReturnSetUp.class));
				if (setUps == null || setUps.size() == 0) {
					sql.setLength(0);
					sql.append("SELECT rts_id,rts_day,rts_second,rts_name,rts_shop_code,t.companyid  ");
					sql.append(" FROM t_vip_returnsetup t");
					sql.append(" WHERE 1=1 ");
					sql.append(" AND rts_shop_code = :shop_upcode");
					sql.append(" AND t.companyid = :companyid ");
					sql.append(" ORDER BY rts_second");
					setUps = namedParameterJdbcTemplate.query(sql.toString(), params, 
							new BeanPropertyRowMapper<>(T_Vip_ReturnSetUp.class));
				}
				if (setUps == null || setUps.size() == 0) {
					resultMap.put(key, 0);
					continue;
				}
				List<String> setDates = new ArrayList<String>();
				for (T_Vip_ReturnSetUp setUp : setUps) {
					setDates.add(DateUtil.getDateAddDays(-setUp.getRts_day()));
				}
				params.put("setDates", setDates);
				sql.setLength(0);
				sql.append(" SELECT COUNT(1)");
				sql.append(" FROM(");
				sql.append(" SELECT sh_shop_code,sh_vip_code,sh_sysdate,sh.companyid ,sh_date,");
				sql.append(" (SELECT MAX(vi_date) FROM t_vip_visit vi WHERE vi_vm_code = sh_vip_code AND vi.companyid = sh.companyid) AS visitDate");
				sql.append(" FROM t_sell_shop sh");
				sql.append(" JOIN t_base_shop sp ON sh.sh_shop_code=sp.sp_code AND sh.companyid=sp.companyid");
				sql.append(" WHERE 1=1");
				sql.append(" AND sh_date IN (:setDates)");
				sql.append(" AND sh_vip_code <> ''");
				sql.append(" AND sh.companyid = :companyid");
				sql.append(" GROUP BY sh_vip_code");
				sql.append(" HAVING (visitDate IS NULL OR visitDate < sh_date)");
				sql.append(" ) t");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}else if(CommonUtil.TODO_IDENTITY_VIPBIRTHDAY.equals(identity)){//会员生日、查询当天生日
				
			}else if (CommonUtil.TODO_IDENTITY_ALLOTSENDWARN.equals(identity)) {// 配货发货单
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_sort_allot t");
				sql.append(" JOIN t_base_shop sp ON sp_code = at_shop_code AND sp.companyid = t.companyid");
				sql.append(getShopSQL(shop_type, 1));
				sql.append(" WHERE 1 = 1");
				sql.append(" AND (at_ar_state IN (1,5) OR (at_ar_state = 0 AND at_applyamount = 0))");
				sql.append(" AND at_type = 0 ");
				sql.append(" AND t.companyid=:companyid");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}else if(CommonUtil.TODO_IDENTITY_ALLOTAPPLYWARN.equals(identity)){//配货申请单
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_sort_allot t");
				sql.append(" JOIN t_base_shop sp ON sp_code = at_shop_code AND sp.companyid = t.companyid");
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_code = :shop_code");
				sql.append(" AND (at_ar_state IN (3) OR (at_ar_state = 0 AND at_applyamount != 0))");
				sql.append(" AND at_type = 0 ");
				sql.append(" AND t.companyid=:companyid");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}else if (CommonUtil.TODO_IDENTITY_ALLOTRETURNCONFIRMWARN.equals(identity)) {// 退货确认单
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_sort_allot t");
				sql.append(" JOIN t_base_shop sp ON sp_code = at_shop_code AND sp.companyid = t.companyid");
				sql.append(getShopSQL(shop_type, 1));
				sql.append(" WHERE 1 = 1");
				sql.append(" AND at_ar_state IN (0,3)");
				sql.append(" AND at_type = 1 ");
				sql.append(" AND t.companyid=:companyid");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}else if (CommonUtil.TODO_IDENTITY_ALLOTRETURNAPPLYWARN.equals(identity)) {// 退货申请单
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_sort_allot t");
				sql.append(" JOIN t_base_shop sp ON sp_code = at_shop_code AND sp.companyid = t.companyid");
				sql.append(" WHERE 1 = 1");
				sql.append(" AND sp_code = :shop_code");
				sql.append(" AND at_ar_state IN (0,1,5)");
				sql.append(" AND at_type = 0 ");
				sql.append(" AND t.companyid=:companyid");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}else if(CommonUtil.TODO_IDENTITY_BUYENTERWARN.equals(identity)){//进货入库单
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_buy_enter t");
				sql.append(" WHERE 1=1");
				sql.append(" AND et_ar_state = 0 ");
				sql.append(" AND et_type = 0 ");
				sql.append(" AND t.companyid=:companyid");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}else if(CommonUtil.TODO_IDENTITY_BUYRETURNWARN.equals(identity)){//退货出库单
				sql.setLength(0);
				sql.append("SELECT COUNT(1)");
				sql.append(" FROM t_buy_enter t");
				sql.append(" WHERE 1=1");
				sql.append(" AND et_ar_state = 0 ");
				sql.append(" AND et_type = 1 ");
				sql.append(" AND t.companyid=:companyid");
				resultMap.put(key, namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class));
			}
			
		}
		return resultMap;
	}
	
}
